/**
 * All coding in PackageUtils.java done by Lidiya.
 */

package Classes;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import teComponents.teJComboBox;
import teComponents.teJList1;
import Classes.*;

// Utility class developed by Lidiya Chhailo

public class PackageUtils 
{
        private DB_utils ut;
        

        
        private Packages getPkgRecord(ResultSet result)
        {
                Packages pkg = new Packages();
                                        
                try 
                {
                        pkg.setPkgId(result.getInt(1));
                        pkg.setPkgName(result.getString(2));
                        pkg.setPkgStartDate(result.getDate(3));
                        pkg.setPkgEndDate(result.getDate(4));
                        pkg.setPkgDesc(result.getString(5));
                        pkg.setPkgbasePrice(result.getDouble(6));
                        pkg.setPkgAgnComm(result.getDouble(7));
                        pkg.getPkgRegion().setRegionID((result.getString(8)));
                } 
                catch (SQLException e) 
                {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                return pkg;
        }
        
        private Regions getRegionsRec(ResultSet result)
        {
                Regions reg = new Regions();
                try {
                        reg.setRegionID(result.getString(1));
                        reg.setRegionName(result.getString(2));
                } 
                catch (SQLException e) 
                {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                
        
                return reg;
        }
        
        
        public Vector<Object> populateRegionsCbo(teJComboBox cbo)       
        {
                Vector<Object> vec = new Vector<Object>();
                setUtils();
                ut.setDbConnection();
                ResultSet rs = ut.getRs("SELECT * FROM Regions");
                try 
                {
                        while (rs.next())
                         {
                                Regions rgns;
                                rgns = getRegionsRec(rs);
                                vec.add(rgns);
                         }
                } 
                catch (SQLException e) 
                {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                vec.add(0, null);
                ut.closeDbConnection();
                return vec;
        }
        
        public int setRegionName(teJComboBox cbo,Regions region)
        {
        
                
                for(int i = 0;i<cbo.getItemCount();i++)
                {
                        Regions rg = (Regions)cbo.getItemAt(i);
                        if(rg != null)
                        {
                                System.out.println(rg.toString());
                           if((rg.getRegionID()).equals(region.getRegionID()))
                          {
                                System.out.println("from setRegionName" + rg.getRegionID());
                                region.setRegionName(rg.getRegionName());
                                return i;
                          }
                        }
                }       
                return -1;
        }
        
        public void processDeleteBtn(teJList1 lst)
        {
                
                Packages pkg = new Packages();
                String sql = "";
                setUtils();
                int index =lst.getSelectedIndex();
                if(index != -1)
                {
                         
                        pkg = (Packages)lst.getSelectedValue();
                        if(pkg != null)
                        {
                                sql = "DELETE FROM Packages WHERE PackageId = '"+pkg.getPkgId()+"'";
                                ut.executeDbRecord(sql);
                                lst.removeElm(index);
                                lst.setSelectedIndex(lst.getListSize());
                                
                                //delete now packages id from packages_products_suppliers
                                sql = "DELETE FROM PACKAGES_PRODUCTS_SUPPLIERS  WHERE PackageId = '"+pkg.getPkgId()+"'";
                                ut.executeDbRecord(sql);
                        }
                        
                        
                }
                else //nothing is highlighted
                {
                        //todo add dialog box with warning message
                }
        }
        
        public void processUpdateBtn(Packages pkg)
        {
                String sql = "";
                
                setUtils();
                
                if(pkg!= null)
                {
                  sql = "UPDATE  Packages SET " 
                        +"PKGNAME = '"+pkg.getPkgName()
                        +"' ,PKGSTARTDATE = TO_DATE('"+pkg.getPkgStartDate()
                        +"','YYYY_MM_DD'),PKGENDDATE = TO_DATE('"+pkg.getPkgEndDate()
                        +"' ,'YYYY_MM_DD'),PKGDESC = '"+pkg.getPkgDesc()
                        +"' ,PKGBASEPRICE = '"+pkg.getPkgbasePrice()
                        +"',PKGAGENCYCOMMISSION = '"+pkg.getPkgAgnComm()
                        +"' ,REGIONID = '"+pkg.getPkgRegion().getRegionID()
                                +"' WHERE PackageId = '"+pkg.getPkgId()+"'";
                  
                      System.out.println(sql);
                                ut.executeDbRecord(sql);
           //here update packages products suppliers
                }
                
        }
        
        public int processAddBtn(Packages pkg,teJList1 lst)
    	{
    		String sql = "";
    		int pkId = -1;
    		setUtils();
    		
    		if(pkg!= null)
    		{
    			
    		  sql = "INSERT INTO Packages VALUES(S_112_1_PACKAGES.nextval,'" 
    		        + pkg.getPkgName() +"',TO_DATE('"
    		        + pkg.getPkgStartDate()+"' ,'YYYY_MM_DD'),TO_DATE('"
    		        +pkg.getPkgEndDate()
    		        +"' ,'YYYY_MM_DD'),'"+pkg.getPkgDesc()
    		        +"' ,'"+pkg.getPkgbasePrice()
    		        +"','"+pkg.getPkgAgnComm()
    		        +"' ,'"+pkg.getPkgRegion().getRegionID()+ "')";
    		  
    		  System.out.println("Insert:"+sql);
    				ut.executeDbRecord(sql);
    				
    		   //Retrieve package to get PackageId
    		    lst.clrList();
    		    sql ="";
    		    sql ="SELECT * FROM Packages WHERE PKGNAME = '"+pkg.getPkgName()+"'";
    		    System.out.println("Select:"+sql);
    			ResultSet rs = ut.getRs(sql);
    				try 
    				{
    					while (rs.next())
    					 {
    						Packages pk;
    						pk = getPkgRecord(rs);	
    						if(pk != null)
    						{
    						  lst.addElem(pk);
    						  pkId = pk.getPkgId();
    						}
    					 }
    				} 
    				catch (SQLException e) 
    				{
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    				
    		}
    		return pkId;
    	}
    	
        public int processUpdateBtn(Packages pkg,teJList1 lst)
    	{
    		String sql = "";
    		int pkId = -1;
    		setUtils();
    		
    		if(pkg!= null)
    		{
    		  sql = "UPDATE  Packages SET " 
    		        +"PKGNAME = '"+pkg.getPkgName()
    		        +"' ,PKGSTARTDATE = TO_DATE('"+pkg.getPkgStartDate()
    		        +"','YYYY_MM_DD'),PKGENDDATE = TO_DATE('"+pkg.getPkgEndDate()
    		        +"' ,'YYYY_MM_DD'),PKGDESC = '"+pkg.getPkgDesc()
    		        +"' ,PKGBASEPRICE = '"+pkg.getPkgbasePrice()
    		        +"',PKGAGENCYCOMMISSION = '"+pkg.getPkgAgnComm()
    		        +"' ,REGIONID = '"+pkg.getPkgRegion().getRegionID()
    		  		+"' WHERE PackageId = '"+pkg.getPkgId()+"'";
    		  
    		      System.out.println(sql);
    				ut.executeDbRecord(sql);
    				 //Retrieve package to get PackageId
    			    lst.clrList();
    			    sql ="";
    			    sql ="SELECT * FROM Packages WHERE PKGNAME = '"+pkg.getPkgName()+"'";
    			    System.out.println("Select:"+sql);
    				ResultSet rs = ut.getRs(sql);
    					try 
    					{
    						while (rs.next())
    						 {
    							Packages pk;
    							pk = getPkgRecord(rs);	
    							if(pk != null)
    							{
    							  lst.addElem(pk);
    							  pkId = pk.getPkgId();
    							}
    						 }
    					} 
    					catch (SQLException e) 
    					{
    						// TODO Auto-generated catch block
    						e.printStackTrace();
    					}
    			
    		}
    		return pkId;
    	}
    	
        public void insertPackProdsupp(int  pkgId, int prsupId)
    	{
    		 String sql  = "";
    		 sql = "INSERT INTO PACKAGES_PRODUCTS_SUPPLIERS VALUES('" +pkgId +  "','" + prsupId + "')";
    		 System.out.println(sql);
    		 ut.executeDbRecord(sql);
    	}
        
        public void addProductsToPkgs(int pkgId,teJList1 lst)
    	{
    		
    		if(pkgId > 0)
    		{
    		  for(int i =0;i<lst.getListSize();i++)
    		  {
    			  Product_Suppliers prsup = (Product_Suppliers)lst.getElm(i);			  
    			  insertPackProdsupp( pkgId,prsup.getProductSupplierId());
    		  }
    		  
    		}
    	}
                
        public void processSrchBtn(teJList1 lst,String sql)
        {
                boolean present = false;
                lst.clrList();
                setUtils();
                ut.setDbConnection();
                System.out.println(sql);
                ResultSet rs = ut.getRs(sql);
                try 
                {                       
                        while (rs.next())
                         {
                                present =true;
                                Packages pkg;
                                pkg = getPkgRecord(rs);
                                lst.addElem(pkg);
                         }
                        if(!present)
                        {
                                lst.addElem("Data Base returned an empty result set");
                        }
                        
                } 
                catch (SQLException e) 
                {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                ut.closeDbConnection();
        }
        
        public void processDelComponentsBtn(teJList1 lst)
        {
                
                Product_Suppliers ps = new Product_Suppliers();
                String sql = "";
                setUtils();
                int index =lst.getSelectedIndex();
                if(index != -1)
                {
                         
                        ps = (Product_Suppliers)lst.getSelectedValue();
                        if(ps != null)
                        {
                                sql = "DELETE FROM PACKAGES_PRODUCTS_SUPPLIERS WHERE PRODUCTSUPPLIERID = '"+ps.getProductSupplierId()+"'";
                                ut.executeDbRecord(sql);
                                lst.removeElm(index);
                                lst.setSelectedIndex(lst.getListSize());
                                
                        }
                        
                        
                }
                else //nothing is highlighted
                {
                        //todo add dialog box with warning message
                }
        }
        
        
        public void populateProductsSuppliersLst(Packages pkg,teJList1 lst)
        {
                boolean present = false;
                lst.clrList();
                String sql = "select  pps.productsupplierid,pps.packageid,p.productid,p.prodname,s.supname ,s.supplierid" +
                      " from  Products  p, suppliers s, products_suppliers ps,"+
                      " packages_products_suppliers pps"+
                       " where pps.packageid = '"+pkg.getPkgId()+"'"+
                        " and  ps.productsupplierid = pps.productsupplierid"+ 
                        " and p.productid = ps.productid"+  
                        " and ps.supplierid = s.supplierid ";
                        
                setUtils();
                ut.setDbConnection();
                
                System.out.println(sql);
                ResultSet rs = ut.getRs(sql);
                try 
                {
                        
                        while (rs.next())
                         {
                                Product_Suppliers ps = new Product_Suppliers();
                                present =true;
                                ps.getProduct().setProductId(rs.getInt("PRODUCTID"));
                                ps.getProduct().setProdName(rs.getString("PRODNAME"));
                                ps.getSupplier().setSupplierId(rs.getInt("SUPPLIERID"));
                                ps.getSupplier().setSupName(rs.getString("SUPNAME"));
                                ps.setProductSupplierId(rs.getInt("PRODUCTSUPPLIERID"));
                                pkg.addPrd_suppId(rs.getInt("PRODUCTSUPPLIERID"));
                                lst.addElem(ps);
                         }
                        if(!present)
                        {
                                lst.addElem("DataBase returned an empty result set");
                        }
                        
                } 
                catch (SQLException e) 
                {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                ut.closeDbConnection();
        }
        

        private DB_utils setUtils()
        {
            if(ut == null)
            {
                ut = new DB_utils();
            }
            return ut;
        }
        
        public void processDelComponentsBtn(teJList1 lst,int pkgId)
    	{
    		
    		Product_Suppliers ps = new Product_Suppliers();
    		String sql = "";
    		setUtils();
    		int index =lst.getSelectedIndex();
    		if(index != -1)
    		{
    			 
    			ps = (Product_Suppliers)lst.getSelectedValue();
    			if(ps != null)
    			{	
    				if(pkgId > 0)
    				{
    				   sql = "DELETE FROM PACKAGES_PRODUCTS_SUPPLIERS WHERE PRODUCTSUPPLIERID = '"+ps.getProductSupplierId()
    				   +"' AND PACKAGEID = '" + pkgId +"'";
    				
    				   ut.executeDbRecord(sql);
    				}
    				lst.removeElm(index);
    				lst.setSelectedIndex(lst.getListSize());
    				
    			}
    			
    			
    		}
    		else //nothing is highlighted
    		{
    			//todo add dialog box with warning message
    		}
    	}
    	
}
